<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<link rel="stylesheet" href="documentation.css" />
	<title>Dauphin Tutorial - Advanced</title> 
</head>
<body>
<h1> Dauphin </h1> <h2> Drop-in front end to MySQL databases. </h2> <h3> <a href="index.html"> Introduction</a> | Tutorial | <a href="advanced.html">Advanced</a> </h3> 

<h4>Example of a Lookup</h4>
<p>A lookup gets a value from another table:</p>

<img src="lookup.gif" alt=""lookup" relationship with another table" width="454" height="133" />
<p>
Shirley Allen has "27" in the "state" column in her row. This equates to "Missouri", the value retrieved from a separate table of states. Shirley lives in Missouri.
</p>
<h4>Example of a Link</h4>
<p>A link is a more complex lookup which gets a value from another table via an intermediary table:</p>
<img src="link.gif" alt="a "linked lookup" which uses two other tables" width="445" height="133" />
<p>
One person can have many degrees, so Shirley, whose ID is "21", is looked up in the table of which people have degrees, and matches two values, "3" and "6". Those values are looked up in a third table, where "3" matches "BA" and "6" matches "MA". Shirley has both a BA and an MA.
</p>
<p>Please note: Dauphin links are only recommended for a structure like the above in which the linking table has <em>only two columns</em>.</p>
<h4>Lookup Fields
</h4>
<p>
If you look at the names and addresses in the <strong>people</strong> database, you'll see that the <strong>state</strong> field holds numbers instead of names. This is because it's designed as a Lookup field.
</p>
<p>
To enable it to look up the values from the second database, <strong>states</strong>, add some more code (a hash of hashes) to <strong>people.cgi</strong>:
</p>
<pre>
lookups => {
    state => {
        table => 'states',
        label => 'longname',
        key   => 'id'
    }
  }
</pre>
<p>
You add this code inside the <code>new()</code> function, so now the whole thing is:
</p>
<pre>
#!/usr/bin/perl
use lib 'module'; 
use Dauphin;
$dauphin = Dauphin->new(
    main_table     => 'people',
    key_field      => 'id',
    display_fields => [ 'firstname', 'lastname', 'phone','state' ],
    sort_field     => 'lastname',
    config_file    => 'database_config.cgi',
    search_fields => [ 'firstname', 'lastname' ],
    lookups => {
    state => {
        table => 'states',
        label => 'longname',
        key   => 'id'
    }
  }
);

</pre>
<h4>Link Fields
</h4>
<p>
You can add a relationship with an external table another, more complex way, to use a many-to-many relationship where data from an external table is mapped to data in this table using an intermediate table.
</p>
<p>
In this case it's the table <strong>degrees</strong>, a list of academic qualifications, and it's linked to <strong>people</strong> with the intermediary table <strong>people_degrees</strong>.
</p>
<p>
To create a relationship of that sort, you add some more code (an array of hashes) to <strong>people.cgi</strong>:
</p>




<pre>
links => [
    {
        field_name     => 'degrees',
        type           => 'multiple',
        external_table => {
            name          => 'degrees',
            key_field     => 'id',
            select_fields => [ 'degree', 'id' ]
        },
        joining_table => {
            name         => 'people_degrees',
            left_column  => 'person',
            right_column => 'degree'
        }
    }
  ]

</pre>
<p>
This is added, as before, part of the <code>new()</code> function. Dauphin's <code>new()</code> function has got pretty complex by now if you've used both these optional relationships, but remember, this should be the only configuration you ever have to do.
</p>
<pre>
#!/usr/bin/perl
use lib 'module';
use Dauphin;
$dauphin = Dauphin->new(
    # non-optional parameters
    main_table     => 'people',
    key_field      => 'id',
    display_fields => [ 'firstname', 'lastname', 'phone', 'state' ],
    sort_field     => 'lastname',
    config_file    => 'database_config.cgi',
    search_fields => [ 'firstname', 'lastname' ],
    
    # optional 'lookups' parameter (HoH)
    lookups       => {
        state => {
            table => 'states',
            label => 'longname',
            key   => 'id'
        }
    },

    # optional 'links' parameter (AoH)
    links => [
        {
            field_name     => 'degrees',
            type           => 'multiple',
            external_table => {
                name          => 'degrees',
                key_field     => 'id',
                select_fields => [ 'degree', 'id' ]
            },
            joining_table => {
                name         => 'people_degrees',
                left_column  => 'person',
                right_column => 'degree'
            }
        }
    ]
);
</pre>




</body>
</html>
